﻿/*
* This software is the confidential and proprietary
* information of NetBay
* Such confidential information should not be disclosed
* NetBay copyright(c) 2010
*/
#region Using
using System;
using System.ComponentModel;
using System.Data;
using NetBay.Core.DataAccess;
using NetBay.Core.Extensions;
using NetBay.Core.Log;
using System.Collections.Generic;
using System.Data.OracleClient;
using Rio.Framework.Extensions;
using Rio.Framework.RioManager;
using NetBay.Core.BusinessEntities;
#endregion

namespace Rio.Doc.DataAccess
{
    public class DA_Folder
    {
        #region Enum
        /// <summary>
        /// List of DB functions to access data
        /// </summary>
        public enum DbFunction
        {
            [DescriptionAttribute("")]
            None,
            [DescriptionAttribute("P_FOLDER_Update")]
            UpdateFolder,
            [DescriptionAttribute("P_FOLDER_Get_ByFolderTypeId")]
            GetFolderByFolderType,
            [DescriptionAttribute("P_FOLDER_Get_ByFldTypeIdSearch")]
            GetFolderByFolderTypeSearch,
            [DescriptionAttribute("P_FOLDER_Get_ByFolderId")]
            GetFolderByFolder,
            [DescriptionAttribute("P_FOLDER_Get_ByFolderIds")]
            GetFolderByFolderIds,
            [DescriptionAttribute("P_FOLDER_Delete")]
            DeleteFolder,
            [DescriptionAttribute("P_FOLDER_Get_ByIndex")]
            IsExist,
            [DescriptionAttribute("P_FOLDER_Add")]
            AddFolder,
            [DescriptionAttribute("P_FOLDER_Get_ByDocumentId")]
            GetFolderByDocumentId,
            [DescriptionAttribute("P_FOLDER_INFO_Update")]
            UpdateFolderInfo,
            [DescriptionAttribute("P_FOLDER_Get_NbDocument")]
            GetDocumentByFolder,
        }
        #endregion

        /// <summary>
        /// Update a Folder's index
        /// </summary>
        /// <param name="p_lFolderTypeId">Foldertype identifier</param>
        /// <param name="p_lFolderId">Folder identifier</param>
        /// <param name="p_sIndexId">CH_INDEX of the index</param>
        /// <param name="p_sValue">Value of the index</param>
        /// <returns>
        /// True if success, false otherwise
        /// </returns>
        public static Boolean UpdateFolderIndex(long p_lFolderTypeId, long p_lFolderId, string p_sIndexId, string p_sValue)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebug("(p_lFolderTypeId<" + p_lFolderTypeId + ">)(p_lFolderId<" + p_lFolderId + ">)(p_sIndexId<" + p_sIndexId + ">)(p_sValue<" + p_sValue + ">)");
                var db = DbHelper.DataBase;

                var dbCommand = db.GetNewCommand(DbFunction.UpdateFolder.szGetDescription(), false);
                db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, p_lFolderTypeId);
                db.AddInParameter(dbCommand, "p_lFolderId", DbType.Int64, p_lFolderId);
                db.AddInParameter(dbCommand, "p_sIndexId", DbType.String, p_sIndexId);
                db.AddInParameter(dbCommand, "p_sValue", DbType.String, p_sValue);

                db.ExecuteNonQuery(dbCommand);
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("DA_FOLDER --> UpdateFolder exception[" + ex.Message + "]");
            }

            return l_bReturn;
        }

        /// <summary>
        /// Get all folders by a folder type identifier
        /// </summary>
        /// <param name="p_lUserId">User ID</param>
        /// <param name="p_lFolderTypeId">Folder Type Iedntifier</param>
        /// <returns>
        /// The list of all folder of a folder type
        /// </returns>
        public static IDataReader GetFolderByFolderTypeId(long p_lUserId, long p_lFolderTypeId)
        {
            LogManager.Instance().LogDebug("(p_lUserId<" + p_lUserId + "> p_lFolderTypeId<" + p_lFolderTypeId + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetFolderByFolderType.szGetDescription());
            db.AddInParameter(dbCommand, "p_userId", DbType.Int64, p_lUserId);
            db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, p_lFolderTypeId);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Get all folders by a folder type identifier
        /// </summary>
        /// <param name="p_lFolderTypeId">Folder Type Iedntifier</param>
        /// <param name="p_lUserId">User ID</param>
        /// <param name="p_sClauseFieldsWhere">The P_S clause fields where.</param>
        /// <returns>
        /// The list of all folder of a folder type
        /// </returns>
        public static IDataReader GetFolderByFolderTypeId(long p_lFolderTypeId, long p_lUserId, string p_sClauseFieldsWhere)
        {
            LogManager.Instance().LogDebug("(p_sClauseFieldsWhere<" + p_sClauseFieldsWhere + "> p_lFolderTypeId<" + p_lFolderTypeId + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetFolderByFolderTypeSearch.szGetDescription());
            db.AddInParameter(dbCommand, "p_userId", DbType.Int64, p_lUserId);
            db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, p_lFolderTypeId);
            db.AddInParameter(dbCommand, "p_sClauseFieldsWhere", DbType.String, p_sClauseFieldsWhere);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Get all folders by a folder type identifier
        /// </summary>
        /// <param name="p_oFolderIds">The p_o folder ids.</param>
        /// <param name="p_oFolderTypeId">The p_o folder type id.</param>
        /// <param name="p_oItemCodes">The p_o item codes.</param>
        /// <returns>
        /// The list of all folder of a folder type
        /// </returns>
        public static IDataReader GetNbDocumentByFolderIds(List<long> p_oFolderIds, long p_oFolderTypeId, List<string> p_oItemCodes)
        {
            string l_sFolderIds = string.Join(",", p_oFolderIds.ToArray());
            string l_sItemCode = "'" + string.Join("','", p_oItemCodes.ToArray()) + "'";

            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetDocumentByFolder.szGetDescription());
            db.AddInParameter(dbCommand, "p_sFodlerIds", DbType.String, l_sFolderIds);
            db.AddInParameter(dbCommand, "p_ItemCodes", DbType.String, l_sItemCode);
            db.AddInParameter(dbCommand, "p_FolderTypeId", DbType.Int64, p_oFolderTypeId);

            return db.ExecuteReader(dbCommand);
        }


        /// <summary>
        /// Gets the folder by document id.
        /// </summary>
        /// <param name="p_sDocumentId">The P_S document id.</param>
        /// <param name="p_lUserId">The P_L user id.</param>
        /// <returns></returns>
        public static IDataReader GetFolderByDocumentId(string p_sDocumentId, long p_lUserId)
        {
            LogManager.Instance().LogDebug("(p_sDocumentId<" + p_sDocumentId + ">) p_lUserId<" + p_lUserId + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetFolderByDocumentId.szGetDescription());
            db.AddInParameter(dbCommand, "p_lDocumentId", DbType.String, p_sDocumentId);
            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, p_lUserId);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Get the folder by a folder identifier
        /// </summary>
        /// <param name="p_lFolderTypeId">Folder Type ID</param>
        /// <param name="p_lFolderId">Folder ID</param>
        /// <param name="p_sFolderIdentifier">The P_S folder identifier.</param>
        /// <returns>
        /// The folder
        /// </returns>
        public static IDataReader GetFolderByFolderId(long p_lFolderTypeId, long p_lFolderId, string p_sFolderIdentifier = "")
        {
            LogManager.Instance().LogDebug("(p_lFolderTypeId<" + p_lFolderTypeId + "> p_lFolderId<" + p_lFolderId + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetFolderByFolder.szGetDescription());
            db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int32, p_lFolderTypeId);
            db.AddInParameter(dbCommand, "p_lFolderId", DbType.Int32, p_lFolderId);
            db.AddInParameter(dbCommand, "p_sFolderIdentifier", DbType.String, p_sFolderIdentifier);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Get the folder by a folder identifier
        /// </summary>
        /// <param name="p_lFolderTypeId">Folder Type ID</param>
        /// <param name="p_lFolderIds">The P_L folder ids.</param>
        /// <param name="p_sFolderIdentifier">The P_S folder identifier.</param>
        /// <returns>
        /// The folder
        /// </returns>
        public static IDataReader GetFolderByFolderIds(long p_lFolderTypeId, string p_lFolderIds, string p_sFolderIdentifier = "")
        {
            LogManager.Instance().LogDebug("(p_lFolderTypeId<" + p_lFolderTypeId + "> p_lFolderId<" + p_lFolderIds + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetFolderByFolderIds.szGetDescription());
            db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, p_lFolderTypeId);
            db.AddInParameter(dbCommand, "p_lFolderId", DbType.String, p_lFolderIds);
            db.AddInParameter(dbCommand, "p_sFolderIdentifier", DbType.String, p_sFolderIdentifier);
            db.AddInParameter(dbCommand, "p_sDateFilter", DbType.String, DateTime.Now.ToShortDateString());

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Delete a Folder 
        /// </summary>
        /// <param name="p_lFolderTypeId">Folder Type identifier</param>
        /// <param name="p_sFolderId">Folder identifier</param>
        public static void DeleteFolder(long p_lFolderTypeId, string p_sFolderId)
        {
            LogManager.Instance().LogDebug("(p_lFolderTypeId<" + p_lFolderTypeId + "> p_sFolderId<" + p_sFolderId + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.DeleteFolder.szGetDescription(), false);
            db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, p_lFolderTypeId);
            db.AddInParameter(dbCommand, "p_sFolderId", DbType.String, p_sFolderId);
            db.ExecuteScalar(dbCommand);
        }

        /// <summary>
        /// Update the folder info
        /// </summary>
        /// <param name="p_lFolderTypeId">Folder Type identifier</param>
        /// <param name="p_lFolderId">The P_L folder id.</param>
        /// <param name="p_sValue">Comment value</param>
        /// <param name="p_sColumn">The P_S column.</param>
        /// <returns></returns>
        public static bool UpdateFolder(long p_lFolderTypeId, long p_lFolderId, string p_sValue, string p_sColumn)
        {
            var l_bReturn = true;
            try
            {
                LogManager.Instance().LogDebug("(p_lFolderTypeId<" + p_lFolderTypeId + "> p_sFolderId<" + p_lFolderId + ">)...");
                var db = DbHelper.DataBase;

                var dbCommand = db.GetNewCommand(DbFunction.UpdateFolderInfo.szGetDescription(), false);
                db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, p_lFolderTypeId);
                db.AddInParameter(dbCommand, "p_lFolderId", DbType.Int64, p_lFolderId);
                db.AddInParameter(dbCommand, "p_sColumn", DbType.String, p_sColumn);
                db.AddInParameter(dbCommand, "p_sValue", DbType.String, p_sValue);
                db.ExecuteScalar(dbCommand);
            }
            catch (Exception ex)
            {
                l_bReturn = false;
                LogManager.Instance().LogError("DA_FOLDER --> UpdateFolder exception[" + ex.Message + "]");
            }

            return l_bReturn;
        }

        /// <summary>
        /// Retrieve whether a folder with a list of valorization exist in a folderType
        /// </summary>
        /// <param name="userId">User ID</param>
        /// <param name="folderTypeId">Folder Type Iedntifier</param>
        /// <param name="tableName">Table name of FolderType</param>
        /// <param name="indexValorization">valorization of index (Index, value)</param>
        /// <param name="useAllCriteria">if set to <c>true</c> [use all criteria].</param>
        /// <returns>
        /// True(Exist) otherwise false
        /// </returns>
        public static long IsFolderExist(long userId, long folderTypeId, string tableName, Dictionary<Index, string> indexValorization, bool useAllCriteria = false)
        {
            long l_bRet = 0;

            try
            {
                LogManager.Instance().LogDebug("(p_lUserId<" + userId + "> p_lFolderTypeId<" + folderTypeId + "> p_szTableName<" + tableName + "> p_oIndexValorization<" + indexValorization.ToString(",") + ">)...");
                var db = DbHelper.DataBase;

                using (var dbCommand = db.GetNewCommand(DbFunction.IsExist.szGetDescription()))
                {
                    // Build conditional SQL clause where
                    var l_szClauseWhere = string.Empty;
                    foreach (KeyValuePair<Index, string> l_oKvp in indexValorization)
                    {
                        if (!string.IsNullOrEmpty(l_oKvp.Value) || useAllCriteria)
                        {
                            //Get a correct value and equality operator for this index (is null or = '...')
                            string SqlValue = l_oKvp.Key.ValueForSQL(l_oKvp.Value);
                            switch ( SettingsManager.cStoreSettings.Parameter_EngineDataBase )
                            {
                                case "Oracle":
                                    l_szClauseWhere += " TO_CHAR(" + tableName + "_CH" + l_oKvp.Key.Name + ") " + l_oKvp.Key.EqualityOperatorByValue(SqlValue) + SqlValue + " AND ";
                                    break;
                                default:
                                    l_szClauseWhere += tableName + "_CH" + l_oKvp.Key.Name + l_oKvp.Key.EqualityOperatorByValue(SqlValue) + SqlValue + " AND ";
                                    break;
                            }
                            //l_szClauseWhere += tableName + "_CH" + l_oKvp.Key.Name + l_oKvp.Key.EqualityOperatorByValue(SqlValue) + SqlValue + " AND ";
                        }
                    }

                    if (!string.IsNullOrEmpty(l_szClauseWhere) && l_szClauseWhere.Length > 0)
                    {
                        l_szClauseWhere = l_szClauseWhere.Substring(0, l_szClauseWhere.Length - 4);
                    }

                    db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, folderTypeId);
                    db.AddInParameter(dbCommand, "p_sFolderValorization", DbType.String, l_szClauseWhere.Trim());
                    db.AddInParameter(dbCommand, "p_userId", DbType.Int64, userId);


                    using (IDataReader userDataReader = db.ExecuteReader(dbCommand))
                    {

                        while (userDataReader.Read())
                        {
                            l_bRet = Convert.ToInt64(userDataReader["Folder_Id"]);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogManager.Instance().LogError("(p_lUserId<" + userId + "> p_lFolderTypeId<" + folderTypeId + "> p_szTableName<" + tableName + "> p_oValorizationIndex<" + indexValorization.ToString(",") + ">) -> Exception" + ex.ToString());
            }
            return l_bRet;
        }

        /// <summary>
        /// Add a folder with a list of valorization in a folderType
        /// </summary>
        /// <param name="p_lUserId">User ID</param>
        /// <param name="p_lFolderTypeId">Folder Type Iedntifier</param>
        /// <param name="p_szIdDoc">The P_SZ id doc.</param>
        /// <param name="p_szTableName">Table name of FolderType</param>
        /// <param name="p_oIndexValorization">valorization of index (Index, value)</param>
        /// <returns>
        /// Retreive the FolderID
        /// </returns>
        public static long AddFolder(long p_lUserId, long p_lFolderTypeId, string p_szIdDoc, string p_szTableName, Dictionary<Index, string> p_oIndexValorization)
        {
            long l_lFolderID = 0;

            try
            {
                LogManager.Instance().LogDebug("(p_lUserId<" + p_lUserId + "> p_lFolderTypeId<" + p_lFolderTypeId + "> p_szIdDoc<" + p_szIdDoc + "> p_oValorizationIndex<" + p_oIndexValorization.ToString(",") + ">)...");
                var db = DbHelper.DataBase;

                using (var dbCommand = db.GetNewCommand(DbFunction.AddFolder.szGetDescription()))
                {

                    // Build conditional SQL clause insert
                    var l_szClauseFieldsInsert = string.Empty;
                    var l_szClauseValuesInsert = string.Empty;

                    // Champs UserId
                    l_szClauseFieldsInsert += p_szTableName + "_USERCRE" + ",";
                    l_szClauseValuesInsert += p_lUserId + ",";

                    // Champs Histo
                    l_szClauseFieldsInsert += p_szTableName + "_HISTO" + ",";
                    l_szClauseValuesInsert += "0" + ",";

                    // Champs DateTime
                    l_szClauseFieldsInsert += p_szTableName + "_DATECRE" + ",";

                    if (SettingsManager.cStoreSettings.Parameter_EngineDataBase == "Oracle")
                    {
                        l_szClauseValuesInsert += "sysdate" + ",";
                    }
                    else
                    {
                        l_szClauseValuesInsert += "Getdate()" + ",";
                    }

                    // Champs UserId
                    l_szClauseFieldsInsert += p_szTableName + "_IDDOC" + ",";
                    l_szClauseValuesInsert += "'" + p_szIdDoc + "'" + ",";

                    // Check whether something to insert
                    if (p_oIndexValorization.Count != 0)
                    {
                        foreach (KeyValuePair<Index, string> l_oKvp in p_oIndexValorization)
                        {
                            //string l_szValue = l_oKvp.Value;
                            l_szClauseFieldsInsert += p_szTableName + "_CH" + l_oKvp.Key.Name + ",";
                            l_szClauseValuesInsert += l_oKvp.Key.ValueForSQL(l_oKvp.Value, true) + ",";
                        }
                        // Remove the ","
                        l_szClauseFieldsInsert = l_szClauseFieldsInsert.TrimEnd(new char[] {','});
                        l_szClauseValuesInsert = l_szClauseValuesInsert.TrimEnd(new char[] {','});

                        // Build Clause insert
                        string l_szClauseInsert = "(" + l_szClauseFieldsInsert + ") values (" + l_szClauseValuesInsert +
                                                  ")";

                        db.AddInParameter(dbCommand, "p_userId", DbType.Int64, p_lUserId);
                        db.AddInParameter(dbCommand, "p_lFolderTypeId", DbType.Int64, p_lFolderTypeId);
                        db.AddInParameter(dbCommand, "p_sInsertValues", DbType.String, l_szClauseInsert);

                        using (IDataReader userDataReader = db.ExecuteReader(dbCommand))
                        {

                            while (userDataReader.Read())
                            {
                                l_lFolderID = Convert.ToInt64(userDataReader["Folder_Id"]);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogManager.Instance().LogError("(p_lUserId<" + p_lUserId + "> p_lFolderTypeId<" + p_lFolderTypeId + "> p_szIdDoc<" + p_szIdDoc + "> p_oValorizationIndex<" + p_oIndexValorization.ToString(",") + ">) -> Exception" + ex.ToString());
            }

            return l_lFolderID;
        }
    }
}